package com.shengzhe.ostudent.utils.jdbc;

import cn.hutool.core.date.DateUtil;
import com.shengzhe.ostudent.entity.base.BaseEntity;
import com.shengzhe.ostudent.utils.LocalDateUtils;
import com.shengzhe.ostudent.utils.OsBeanUtils;
import lombok.Data;
import org.apache.commons.beanutils.BeanUtils;

import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

/**
 * @author s.li
 * @describe mysql条件对象
 * @date 2021/4/6 9:40
 */
@Data
public class WhereSql {
    private StringBuilder whereSql;

    public static WhereSql emptySql() {
        return new WhereSql("");
    }

    public static WhereSql andId(Long id) {
        return new WhereSql().and("id", id);
    }

    public WhereSql() {
        whereSql = new StringBuilder(" where 1=1 ");
    }

    /**
     * 构建方法，传入Condition类，构建and条件sql，暂不支持小数
     * 注意本方法只能构建一整串的and条件，定制化的条件sql，请在service中实现getWhereSql方法
     *
     * @param condition Condition类
     */
    public static WhereSql buildAnd(Object condition) {
        WhereSql where = new WhereSql();


        Class<?> clazz = condition.getClass();
        // 简单判断一下，如果类名中没有 Condition 则不认为是一个条件构造类，返回空对象
        if (!clazz.getSimpleName().contains("Condition")) {
            return new WhereSql();
        }
        // 只构建实体类自己的条件
        while (!(clazz.getSuperclass().getSimpleName().equals("BaseEntity"))) {
            clazz = clazz.getSuperclass();
        }
        // 使用反射取出所有有参的字段进行构建
        for (Field field : OsBeanUtils.getPrivateFields(clazz)) {
            // 允许访问私有字段
            field.setAccessible(true);

            String name = field.getName();
            Object value;

            try {
                value = field.get(condition);
                // 无值进入下一次循环
                if (Objects.isNull(value)) {
                    continue;
                }
                // 有值，且字段名是分页参数 不进行参数构建
                if (name.equals("pageNum") || name.equals("pageSize")) {
                    continue;
                }

                Class<?> type = field.getType();
                if (type == Integer.class || type == Long.class || type == Short.class) {
                    where.and(name, Long.parseLong(String.valueOf(value)));
                } else if (type == String.class) {
                    where.and(name, String.valueOf(value));
                } else if ((type == Date.class)) {
                    where.and(name, (Date) value);
                }
//                where.and(name, String.valueOf(value));
            } catch (IllegalArgumentException | IllegalAccessException e) {
                throw new RuntimeException("@Error when getting value of " + name, e);
            }
        }
        return where;
    }

    public WhereSql(String where) {
        this.whereSql = new StringBuilder(where);
    }

    public WhereSql(StringBuilder where) {
        this.whereSql = new StringBuilder(where);
    }

    //and条件============

    public WhereSql and(String columnName, String value) {
        value = handleSingleQuotes(value);
        whereSql.append(" and `").append(columnName).append("` = '").append(value).append("'");
        return this;
    }

    public WhereSql and(String columnName, int value) {
        whereSql.append(" and `").append(columnName).append("` = ").append(value);
        return this;
    }

    public WhereSql and(String columnName, long value) {
        whereSql.append(" and `").append(columnName).append("` = ").append(value);
        return this;
    }

    public WhereSql and(String columnName, float value) {
        whereSql.append(" and `").append(columnName).append("` = ").append(value);
        return this;
    }

    public WhereSql and(String columnName, double value) {
        whereSql.append(" and `").append(columnName).append("` = ").append(value);
        return this;
    }

    public WhereSql and(String columnName, short value) {
        whereSql.append(" and `").append(columnName).append("` = ").append(value);
        return this;
    }

    public WhereSql and(String columnName, Date value) {
        return and(columnName, value, LocalDateUtils.YYYY_MM_DD_HH_MM_SS);
    }

    public WhereSql and(String columnName, Date value, String format) {
        whereSql.append(" and `").append(columnName).append("` = '").append(DateUtil.format(value, format)).append("'");
        return this;
    }

    public WhereSql andIsNotNull(String columnName) {
        whereSql.append(" and `").append(columnName).append("` is not null");
        return this;
    }

    public WhereSql andIsNull(String columnName) {
        whereSql.append(" and `").append(columnName).append("` is null");
        return this;
    }

    /**
     * and !=  =====================
     */
    public WhereSql andNot(String columnName, String value) {
        value = handleSingleQuotes(value);
        whereSql.append(" and `").append(columnName).append("` != '").append(value).append("'");
        return this;
    }

    public WhereSql andNot(String columnName, int value) {
        whereSql.append(" and `").append(columnName).append("` != ").append(value);
        return this;
    }

    public WhereSql andNot(String columnName, long value) {
        whereSql.append(" and `").append(columnName).append("` != ").append(value);
        return this;
    }

    public WhereSql andNot(String columnName, float value) {
        whereSql.append(" and `").append(columnName).append("` != ").append(value);
        return this;
    }

    public WhereSql andNot(String columnName, double value) {
        whereSql.append(" and `").append(columnName).append("` != ").append(value);
        return this;
    }

    public WhereSql andNot(String columnName, short value) {
        whereSql.append(" and `").append(columnName).append("` != ").append(value);
        return this;
    }

    //or条件============

    public WhereSql or(String columnName, String value) {
        value = handleSingleQuotes(value);
        whereSql.append(" or `").append(columnName).append("` = '").append(value).append("'");
        return this;
    }

    public WhereSql or(String columnName, int value) {
        whereSql.append(" or `").append(columnName).append("` = ").append(value);
        return this;
    }

    public WhereSql or(String columnName, long value) {
        whereSql.append(" or `").append(columnName).append("` = ").append(value);
        return this;
    }

    public WhereSql or(String columnName, float value) {
        whereSql.append(" or `").append(columnName).append("` = ").append(value);
        return this;
    }

    public WhereSql or(String columnName, double value) {
        whereSql.append(" or `").append(columnName).append("` = ").append(value);
        return this;
    }

    public WhereSql or(String columnName, short value) {
        whereSql.append(" or `").append(columnName).append("` = ").append(value);
        return this;
    }

    public WhereSql or(String columnName, Date value) {
        return or(columnName, value, LocalDateUtils.YYYY_MM_DD_HH_MM_SS);
    }

    public WhereSql or(String columnName, Date value, String format) {
        whereSql.append(" or `").append(columnName).append("` = '").append(DateUtil.format(value, format)).append("'");
        return this;
    }

    /**
     * and大于================
     */
    public WhereSql andGt(String columnName, int value) {
        whereSql.append(" and `").append(columnName).append("` > ").append(value);
        return this;
    }

    public WhereSql andGt(String columnName, long value) {
        whereSql.append(" and `").append(columnName).append("` > ").append(value);
        return this;
    }

    public WhereSql andGt(String columnName, float value) {
        whereSql.append(" and `").append(columnName).append("` > ").append(value);
        return this;
    }

    public WhereSql andGt(String columnName, double value) {
        whereSql.append(" and `").append(columnName).append("` > ").append(value);
        return this;
    }

    public WhereSql andGt(String columnName, short value) {
        whereSql.append(" and `").append(columnName).append("` > ").append(value);
        return this;
    }

    public WhereSql andGt(String columnName, Date value) {
        return andGt(columnName, value, LocalDateUtils.YYYY_MM_DD_HH_MM_SS);
    }

    public WhereSql andGt(String columnName, Date value, String format) {
        whereSql.append(" and `").append(columnName).append("` > '").append(DateUtil.format(value, format)).append("'");
        return this;
    }

    /**
     * and大于等于================
     */
    public WhereSql andGtEq(String columnName, int value) {
        whereSql.append(" and `").append(columnName).append("` >= ").append(value);
        return this;
    }

    public WhereSql andGtEq(String columnName, long value) {
        whereSql.append(" and `").append(columnName).append("` >= ").append(value);
        return this;
    }

    public WhereSql andGtEq(String columnName, float value) {
        whereSql.append(" and `").append(columnName).append("` >= ").append(value);
        return this;
    }

    public WhereSql andGtEq(String columnName, double value) {
        whereSql.append(" and `").append(columnName).append("` >= ").append(value);
        return this;
    }

    public WhereSql andGtEq(String columnName, short value) {
        whereSql.append(" and `").append(columnName).append("` >= ").append(value);
        return this;
    }

    public WhereSql andGtEq(String columnName, Date value) {
        return andGtEq(columnName, value, LocalDateUtils.YYYY_MM_DD_HH_MM_SS);
    }

    public WhereSql andGtEq(String columnName, Date value, String format) {
        whereSql.append(" and `").append(columnName).append("` >= '").append(DateUtil.format(value, format)).append("'");
        return this;
    }

    /**
     * and小于================
     */
    public WhereSql andLt(String columnName, int value) {
        whereSql.append(" and `").append(columnName).append("` < ").append(value);
        return this;
    }

    public WhereSql andLt(String columnName, long value) {
        whereSql.append(" and `").append(columnName).append("` < ").append(value);
        return this;
    }

    public WhereSql andLt(String columnName, float value) {
        whereSql.append(" and `").append(columnName).append("` < ").append(value);
        return this;
    }

    public WhereSql andLt(String columnName, double value) {
        whereSql.append(" and `").append(columnName).append("` < ").append(value);
        return this;
    }

    public WhereSql andLt(String columnName, short value) {
        whereSql.append(" and `").append(columnName).append("` < ").append(value);
        return this;
    }

    public WhereSql andLt(String columnName, Date value) {
        return andLt(columnName, value, LocalDateUtils.YYYY_MM_DD_HH_MM_SS);
    }

    public WhereSql andLt(String columnName, Date value, String format) {
        whereSql.append(" and `").append(columnName).append("` < '").append(DateUtil.format(value, format)).append("'");
        return this;
    }

    /**
     * and小于等于================
     */
    public WhereSql andLtEq(String columnName, int value) {
        whereSql.append(" and `").append(columnName).append("` <= ").append(value);
        return this;
    }

    public WhereSql andLtEq(String columnName, long value) {
        whereSql.append(" and `").append(columnName).append("` <= ").append(value);
        return this;
    }

    public WhereSql andLtEq(String columnName, float value) {
        whereSql.append(" and `").append(columnName).append("` <= ").append(value);
        return this;
    }

    public WhereSql andLtEq(String columnName, double value) {
        whereSql.append(" and `").append(columnName).append("` <= ").append(value);
        return this;
    }

    public WhereSql andLtEq(String columnName, short value) {
        whereSql.append(" and `").append(columnName).append("` <= ").append(value);
        return this;
    }

    public WhereSql andLtEq(String columnName, Date value) {
        return andLtEq(columnName, value, LocalDateUtils.YYYY_MM_DD_HH_MM_SS);
    }

    public WhereSql andLtEq(String columnName, Date value, String format) {
        whereSql.append(" and `").append(columnName).append("` <= '").append(DateUtil.format(value, format)).append("'");
        return this;
    }

    /**
     * or大于================
     */
    public WhereSql orGt(String columnName, int value) {
        whereSql.append(" or `").append(columnName).append("` > ").append(value);
        return this;
    }

    public WhereSql orGt(String columnName, long value) {
        whereSql.append(" or `").append(columnName).append("` > ").append(value);
        return this;
    }

    public WhereSql orGt(String columnName, float value) {
        whereSql.append(" or `").append(columnName).append("` > ").append(value);
        return this;
    }

    public WhereSql orGt(String columnName, double value) {
        whereSql.append(" or `").append(columnName).append("` > ").append(value);
        return this;
    }

    public WhereSql orGt(String columnName, short value) {
        whereSql.append(" or `").append(columnName).append("` > ").append(value);
        return this;
    }

    public WhereSql orGt(String columnName, Date value) {
        return orGt(columnName, value, LocalDateUtils.YYYY_MM_DD_HH_MM_SS);
    }

    public WhereSql orGt(String columnName, Date value, String format) {
        whereSql.append(" or `").append(columnName).append("` > '").append(DateUtil.format(value, format)).append("'");
        return this;
    }

    /**
     * or大于等于================
     */
    public WhereSql orGtEq(String columnName, int value) {
        whereSql.append(" or `").append(columnName).append("` >= ").append(value);
        return this;
    }

    public WhereSql orGtEq(String columnName, long value) {
        whereSql.append(" or `").append(columnName).append("` >= ").append(value);
        return this;
    }

    public WhereSql orGtEq(String columnName, float value) {
        whereSql.append(" or `").append(columnName).append("` >= ").append(value);
        return this;
    }

    public WhereSql orGtEq(String columnName, double value) {
        whereSql.append(" or `").append(columnName).append("` >= ").append(value);
        return this;
    }

    public WhereSql orGtEq(String columnName, short value) {
        whereSql.append(" or `").append(columnName).append("` >= ").append(value);
        return this;
    }

    public WhereSql orGtEq(String columnName, Date value) {
        return orGtEq(columnName, value, LocalDateUtils.YYYY_MM_DD_HH_MM_SS);
    }

    public WhereSql orGtEq(String columnName, Date value, String format) {
        whereSql.append(" or `").append(columnName).append("` >= '").append(DateUtil.format(value, format)).append("'");
        return this;
    }

    /**
     * or小于================
     */
    public WhereSql orLt(String columnName, int value) {
        whereSql.append(" or `").append(columnName).append("` < ").append(value);
        return this;
    }

    public WhereSql orLt(String columnName, long value) {
        whereSql.append(" or `").append(columnName).append("` < ").append(value);
        return this;
    }

    public WhereSql orLt(String columnName, float value) {
        whereSql.append(" or `").append(columnName).append("` < ").append(value);
        return this;
    }

    public WhereSql orLt(String columnName, double value) {
        whereSql.append(" or `").append(columnName).append("` < ").append(value);
        return this;
    }

    public WhereSql orLt(String columnName, short value) {
        whereSql.append(" or `").append(columnName).append("` < ").append(value);
        return this;
    }

    public WhereSql orLt(String columnName, Date value) {
        return orLt(columnName, value, LocalDateUtils.YYYY_MM_DD_HH_MM_SS);
    }

    public WhereSql orLt(String columnName, Date value, String format) {
        whereSql.append(" or `").append(columnName).append("` < '").append(DateUtil.format(value, format)).append("'");
        return this;
    }

    /**
     * or小于等于================
     */
    public WhereSql orLtEq(String columnName, int value) {
        whereSql.append(" or `").append(columnName).append("` <= ").append(value);
        return this;
    }

    public WhereSql orLtEq(String columnName, long value) {
        whereSql.append(" or `").append(columnName).append("` <= ").append(value);
        return this;
    }

    public WhereSql orLtEq(String columnName, float value) {
        whereSql.append(" or `").append(columnName).append("` <= ").append(value);
        return this;
    }

    public WhereSql orLtEq(String columnName, double value) {
        whereSql.append(" or `").append(columnName).append("` <= ").append(value);
        return this;
    }

    public WhereSql orLtEq(String columnName, short value) {
        whereSql.append(" or `").append(columnName).append("` <= ").append(value);
        return this;
    }

    public WhereSql orLtEq(String columnName, Date value) {
        return orLtEq(columnName, value, LocalDateUtils.YYYY_MM_DD_HH_MM_SS);
    }

    public WhereSql orLtEq(String columnName, Date value, String format) {
        whereSql.append(" or `").append(columnName).append("` <= '").append(DateUtil.format(value, format)).append("'");
        return this;
    }

    /**
     * and in
     */
    public WhereSql andIn(String columnName, Collection<String> values) {
        String val = values.stream().collect(Collectors.joining("','", "'", "'"));
        whereSql.append(" and `").append(columnName).append("` in (").append(val).append(")");
        return this;
    }

    /**
     * and in
     */
    public WhereSql andIntIn(String columnName, Collection<Integer> values) {
        String val = values.stream().map(String::valueOf).collect(Collectors.joining(","));
        whereSql.append(" and `").append(columnName).append("` in (").append(val).append(")");
        return this;
    }

    public WhereSql andLongIn(String columnName, Collection<Long> values) {
        String val = values.stream().map(String::valueOf).collect(Collectors.joining(","));
        whereSql.append(" and `").append(columnName).append("` in (").append(val).append(")");
        return this;
    }

    public WhereSql originalAndIn(String columnName, Collection<String> values) {
        String val = values.stream().collect(Collectors.joining("','", "'", "'"));
        whereSql.append(" and ").append(columnName).append(" in (").append(val).append(")");
        return this;
    }

    /**
     * and not in
     */
    public WhereSql andNotIn(String columnName, Collection<String> values) {
        String val = values.stream().collect(Collectors.joining("','", "'", "'"));
        whereSql.append(" and `").append(columnName).append("` not in (").append(val).append(")");
        return this;
    }

    public WhereSql andNotIntIn(String columnName, Collection<Integer> values) {
        String val = values.stream().map(e -> String.valueOf(e)).collect(Collectors.joining(","));
        whereSql.append(" and `").append(columnName).append("` not in (").append(val).append(")");
        return this;
    }

    /**
     * or in
     */
    public WhereSql orIn(String columnName, Collection<String> values) {
        String val = values.stream().collect(Collectors.joining("','", "'", "'"));
        whereSql.append(" or `").append(columnName).append("` in (").append(val).append(")");
        return this;
    }

    /**
     * and like
     */
    public WhereSql andLike(String columnName, String value) {
        value = handleSingleQuotes(value);
        whereSql.append(" and `").append(columnName).append("` like '%").append(value).append("%'");
        return this;
    }

    /**
     * and Left like
     */
    public WhereSql andLeftLike(String columnName, String value) {
        value = handleSingleQuotes(value);
        whereSql.append(" and `").append(columnName).append("` like '%").append(value).append("'");
        return this;
    }

    /**
     * and right like
     */
    public WhereSql andRightLike(String columnName, String value) {
        value = handleSingleQuotes(value);
        whereSql.append(" and `").append(columnName).append("` like '").append(value).append("%'");
        return this;
    }

    /**
     * or like
     */
    public WhereSql orLike(String columnName, String value) {
        value = handleSingleQuotes(value);
        whereSql.append(" or `").append(columnName).append("` like '%").append(value).append("%'");
        return this;
    }

    /**
     * or Left like
     */
    public WhereSql orLeftLike(String columnName, String value) {
        value = handleSingleQuotes(value);
        whereSql.append(" or `").append(columnName).append("` like '%").append(value).append("'");
        return this;
    }

    /**
     * or right like
     */
    public WhereSql orRightLike(String columnName, String value) {
        value = handleSingleQuotes(value);
        whereSql.append(" or `").append(columnName).append("` like '").append(value).append("%'");
        return this;
    }

    /**
     * and多个or条件，如果 and(columnName=value or columnName=value)
     *
     * @param columnName 字段名
     * @param values     条件值列表
     * @return
     */
    public WhereSql andToMultipleOr(String columnName, Collection<String> values) {
        String val = values.stream().collect(Collectors.joining("' or `" + columnName + "`='", "`" + columnName + "` = '", "'"));
        whereSql.append(" and (").append(val).append(")");
        return this;
    }

    /**
     * and多个or条件，如果 and(columnName like '%value%' or columnName like '%value%')
     *
     * @param columnName 字段名
     * @param values     条件值列表
     * @return WhereSql
     */
    public WhereSql andToMultipleOrLike(String columnName, Collection<String> values) {
        String val = values.stream().collect(Collectors.joining("%' or `" + columnName + "` like '%", "`" + columnName + "` like '%", "%'"));
        whereSql.append(" and (").append(val).append(")");
        return this;
    }

    /**
     * and多个or条件，如果 and(columnName like '%value%' or columnName like '%value%')
     *
     * @param columnName 字段名
     * @param values     条件值列表
     * @return WhereSql
     */
    public WhereSql andToMultipleOrLikeComma(String columnName, Collection<String> values) {
        String val = values.stream().collect(Collectors.joining(",%' or `" + columnName + "` like '%,", "`" + columnName + "` like '%,", ",%'"));
        whereSql.append(" and (").append(val).append(")");
        return this;
    }

    /***
     * 追加条件
     * @param sql SQL条件
     * @return WhereSql
     */
    public WhereSql append(String sql) {
        whereSql.append(sql);
        return this;
    }

    public WhereSql putValue(String value) {
        value = handleSingleQuotes(value);
        whereSql.append("'").append(value).append("'");
        return this;
    }

    public WhereSql defOrderDesc() {
        return orderDesc("sort");
    }

    public WhereSql orderDesc(String columnName) {
        whereSql.append(" order by `").append(columnName).append("` desc ");
        return this;
    }

    public WhereSql orderDesc(String... columnName) {
        whereSql.append(" order by ");
        IntStream.range(0, columnName.length).forEach(index -> {
            String name = columnName[index];
            whereSql.append("`").append(name).append("` desc ");
            if (index < columnName.length - 1) {
                whereSql.append(",");
            }
        });
        return this;
    }

    public WhereSql defOrderAsc() {
        return orderAsc("sort");
    }

    public WhereSql orderAsc(String columnName) {
        whereSql.append(" order by `").append(columnName).append("` asc ");
        return this;
    }

    public WhereSql orderAsc(String... columnName) {
        whereSql.append(" order by ");
        IntStream.range(0, columnName.length).forEach(index -> {
            String name = columnName[index];
            whereSql.append("`").append(name).append("` asc ");
            if (index < columnName.length - 1) {
                whereSql.append(",");
            }
        });
        return this;
    }

    public WhereSql group(String... columnNames) {
        String columnName = Arrays.stream(columnNames).collect(Collectors.joining("`,`", "`", "`"));
        whereSql.append(" group by ").append(columnName);
        return this;
    }

    public String getStringWhereSql() {
        return this.getWhereSql().toString();
    }

    /**
     * 替换处理单引号
     *
     * @param value 值
     * @return 处理后的值
     */
    private String handleSingleQuotes(String value) {
        return value.replaceAll("'", "\\\\'");
    }
}
